﻿using Dapper;
using FRMS.Helper;
using FRMS.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace FRMS.DAL
{
    public class RoomDAL
    {
        public bool AddRoom(RoomInfo roomInfo)
        {
            string strSql = string.Format("INSERT INTO room_info (BuildID,RoomName,projectID)VALUES({0},'{1}',{2})", roomInfo.BuildID, roomInfo.RoomName,roomInfo.projectID);
            return SqlHelper<RoomInfo>.SqlExecute(strSql);
        }

        public int AddRoom_1(RoomInfo roomInfo)
        {
            string strSql = string.Format("INSERT INTO room_info (BuildID,RoomName,projectID)VALUES({0},'{1}',{2});select @@IDENTITY;", roomInfo.BuildID, roomInfo.RoomName, roomInfo.projectID);
            return SqlHelper<RoomInfo>.SqlScalar(strSql);
        }

        public bool ImportRoom(RoomInfo roomInfo)
        {
            string strSql = string.Format("INSERT INTO room_info (BuildID,RoomName,projectID)VALUES((SELECT ID FROM building_info WHERE BuildingName='{0}' AND projectID={2}),'{1}',{2})", roomInfo.BuildingName, roomInfo.RoomName,roomInfo.projectID);
            return SqlHelper<RoomInfo>.SqlExecute(strSql);
        }

        public bool UpdateRoom(RoomInfo roomInfo)
        {
            string strSql = string.Format("UPDATE room_info SET RoomName='{0}' WHERE ID={1}", roomInfo.RoomName, roomInfo.ID);
            return SqlHelper<RoomInfo>.SqlExecute(strSql);
        }

        public bool DelRoom(RoomInfo roomInfo)
        {
            string strSql = "UPDATE room_info SET IsDelete=1 WHERE ID=" + roomInfo.ID;
            return SqlHelper<RoomInfo>.SqlExecute(strSql);
        }

        public List<RoomInfo> QueryRoomInfo(RoomInfo roomInfo)
        {
            string strSql = "SELECT * FROM vi_room";
            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(roomInfo.RoomName))
            {
                sqlwhere += " RoomName like '%" + roomInfo.RoomName + "%' AND";
            }
            if (roomInfo.BuildID > 0)
            {
                sqlwhere += " BuildID=" + roomInfo.BuildID + " AND";
            }
            sqlwhere += " IsDelete=0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (roomInfo.Recordhead > -1 && roomInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + roomInfo.Recordhead + "," + roomInfo.Recordtail;
            }

            return SqlHelper<RoomInfo>.SqlList(strSql);
        }

        public int QueryRoomInfoCount(RoomInfo roomInfo)
        {
            string strSql = "SELECT COUNT(1) FROM room_info";
            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(roomInfo.RoomName))
            {
                sqlwhere += " RoomName like '%" + roomInfo.RoomName + "%' AND";
            }
            if (roomInfo.BuildID > 0)
            {
                sqlwhere += " BuildID=" + roomInfo.BuildID + " AND";
            }
            sqlwhere += " IsDelete=0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<RoomInfo>.SqlScalar(strSql);
        }

        public bool CheckOwner(RoomInfo roomInfo)
        {
            string strSql = "SELECT COUNT(1) FROM owner_info where RoomID=" + roomInfo.ID;

            int count = SqlHelper<RoomInfo>.SqlScalar(strSql);
            if (count > 0)
            {
                return false;
            }
            else
            {
                return true;
            }

        }

        public List<RoomInfo> QueryRoomList(RoomInfo roomInfo)
        {
            string strSql = "SELECT * FROM vi_room";
            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(roomInfo.RoomName))
            {
                sqlwhere += " RoomName like '%" + roomInfo.RoomName + "%' AND";
            }
            if (!string.IsNullOrEmpty(roomInfo.BuildingName))
            {
                sqlwhere += " BuildingName like '%" + roomInfo.BuildingName + "%' AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (roomInfo.Recordhead > -1 && roomInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + roomInfo.Recordhead + "," + roomInfo.Recordtail;
            }

            return SqlHelper<RoomInfo>.SqlList(strSql);
        }

        public int QueryRoomListCount(RoomInfo roomInfo)
        {
            string strSql = "SELECT COUNT(1) FROM vi_room";
            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(roomInfo.RoomName))
            {
                sqlwhere += " RoomName like '%" + roomInfo.RoomName + "%' AND";
            }
            if (!string.IsNullOrEmpty(roomInfo.BuildingName))
            {
                sqlwhere += " BuildingName like '%" + roomInfo.BuildingName + "%' AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<RoomInfo>.SqlScalar(strSql);
        }

        public RoomInfo LoadRoom(int id)
        {
            string strSql = "SELECT * FROM room_info WHERE ID=" + id;
            return SqlHelper<RoomInfo>.SqlSingleData(strSql);
        }

        public int CheckRoom(int buildid, string name, int id)
        {
            string strSql = "SELECT COUNT(1) FROM room_info WHERE RoomName='" + name + "' AND BuildID=" + buildid + " AND ID!=" + id;
            return SqlHelper<RoomInfo>.SqlScalar(strSql);
        }
        public int CheckRoomID(int buildid, string name, int id)
        {
            string strSql = "SELECT ID FROM room_info WHERE RoomName='" + name + "' AND BuildID=" + buildid + " AND ID!=" + id;
            return SqlHelper<RoomInfo>.SqlScalar(strSql);
        }

        public int CheckImport(string buildname, string roomname)
        {
            string strSql = "SELECT COUNT(1) FROM room_info WHERE BuildID=(SELECT ID FROM building_info WHERE BuildingName='" + buildname + "') AND RoomName='" + roomname + "'";
            return SqlHelper<RoomInfo>.SqlScalar(strSql);
        }

        public List<SyncReturnBuildInfo> QueryAllRoomInfo(int projectID)
        {
            string strSql = "SELECT ID AS RoomID,RoomName,BuildID,BuildingName FROM vi_room where IsDelete=0 AND projectID=" + projectID;
            
            return SqlHelper<SyncReturnBuildInfo>.SqlList(strSql);
        }
    }
}
